/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package photoshare.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import photoshare.dto.FunctionDTO;
import photoshare.util.DBUtil;

/**
 *
 * @author PhotoShare
 */
public class FunctionDAO {

    private String READ_BY_ALL = "SELECT * FROM FUNCTIONS";
    private String READ_BY_ID = "SELECT * FROM FUNCTIONS WHERE FunctionID = ?";
    private String CREATE = "INSERT INTO FUNCTIONS VALUES(?,?,?)";
    private String DELETE_BY_ID = "UPDATE FUNCTIONS SET IsACTIVE= 0 WHERE FunctionID = ?";
    private String UPDATE_BY_ID = "UPDATE FUNCTIONS SET PASS = ? WHERE FunctionID = ?";
    private String READ_BY_ROLE = "select functions.url,functions.description from functions join rolefunction on functions.functionid = rolefunction.functionid join roles on rolefunction.roleid = roles.roleid where roles.roleid = ?";

    private Connection conn = null;
    private PreparedStatement pst = null;

    public FunctionDAO() {
    }

    public boolean create(FunctionDTO functionDTO) throws SQLException {
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(CREATE);
            pst.setString(1, functionDTO.getAction());
            pst.setString(2, functionDTO.getDescription());
            int num = 0;
            num = pst.executeUpdate();
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception ex) {
            return false;
        } finally {
            DBUtil.closeConn(conn);
            pst.close();
        }
    }

    public ArrayList<FunctionDTO> readAll() {
        try {
            ArrayList<FunctionDTO> arr = null;
            arr = new ArrayList<FunctionDTO>();
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(READ_BY_ALL);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                FunctionDTO dto = new FunctionDTO();
                dto.setAction(rs.getString(2));
                dto.setDescription(rs.getString(3));
                arr.add(dto);
            }
            return arr;
        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            DBUtil.closeConn(conn);
        }
    }

    public FunctionDTO readById(Integer functionId) {
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(READ_BY_ID);
            pst.setInt(1, functionId);
            //pst = conn.prepareStatement("select * from acc WHERE accountID = 'admin'");
            ResultSet rs = pst.executeQuery();
            FunctionDTO functionDTO = new FunctionDTO();
            while (rs.next()) {
                functionDTO.setAction(rs.getString(2));
                functionDTO.setDescription(rs.getString(3));
            }
            return functionDTO;
        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            DBUtil.closeConn(conn);
        }
    }

    public ArrayList<FunctionDTO> readByRole(Integer roleId) {
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(READ_BY_ROLE);
            pst.setInt(1, roleId);
            //pst = conn.prepareStatement("select * from acc WHERE accountID = 'admin'");
            ResultSet rs = pst.executeQuery();
             ArrayList<FunctionDTO> arr = null;
            arr = new ArrayList<FunctionDTO>();
            while (rs.next()) {
//                FunctionDTO functionDTO = new FunctionDTO();
//                functionDTO.setAction(rs.getString(2));
//                functionDTO.setDescription(rs.getString(3));
//                arr.add(functionDTO);
                FunctionDTO dto = new FunctionDTO();
                dto.setAction(rs.getString(1));
                dto.setDescription(rs.getString(2));
                arr.add(dto);
            }
            return arr;
        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            DBUtil.closeConn(conn);
        }
    }

    public boolean updateById(FunctionDTO functionDTO, Integer functionId) throws SQLException {
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(UPDATE_BY_ID);
            //Tham so can Update trong cau lenh SQL
            pst.setString(1, functionDTO.getAction());
            //Tham so dieu kien cua cau lenh Update
            pst.setInt(2, functionId);
            int num = 0;
            num = pst.executeUpdate();
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
            return false;
        } finally {
            DBUtil.closeConn(conn);
            pst.close();
        }
    }

    public boolean deleteById(Integer functionId) throws SQLException {

        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(DELETE_BY_ID);
            //Tham so dieu kien cho cau lenh Update
            pst.setInt(1, functionId);
            int num = 0;
            num = pst.executeUpdate();
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception ex) {
            return false;
        } finally {
            DBUtil.closeConn(conn);
            pst.close();
        }
    }

}
